from django.shortcuts import render
from django.template import Template, Context  # 调用template, 以及上下文处理器
from django.http import HttpResponse


def work(request):
    data = [
        {'name': '小宋', 'age': 20, 'score': 90},
        {'name': '卡坤', 'age': 20, 'score': 80},
        {'name': '赵六', 'age': 20, 'score': 60},
        {'name': '芳芳', 'age': 20, 'score': 50},
        {'name': '小小', 'age': 20, 'score': 30},
        {'name': '艾卡', 'age': 20, 'score': 40},
        {'name': '小刚', 'age': 20, 'score': 98}
    ]

    for i in data:
        if i['score'] >= 80:
            i['lv'] = '优秀'
        elif i['score'] >= 60:
            i['lv'] = '良好'
        else:
            i['lv'] = '不及格'

    data1 = [
        {'name': 1, 'p': ['a', 'b', 'c']},
        {'name': 2, 'p': ['d', 'e', 'f']}
    ]

    return render(request, 'work.html', {'is_login': True, 'data': data, 'data1': data1})


def homework(request):
    blog_entries = [
        {'title': '秋登兰山寄张五', 'poet': '孟浩然',
         'poem': "北山白云里，隐者自怡悦。相望试登高，心随雁飞灭。愁因薄暮起，兴是清秋发。时见归村人，沙行渡头歇。天边树若荠，江畔洲如月。何当载酒来，共醉重阳节。",
         'is_published': True},
        {'title': '赠卫八处士', 'poet': '杜甫',
         'poem': "人生不相见，动如参与商。今夕复何夕，共此灯烛光少壮能几时？鬓发各已苍！访旧半为鬼，惊呼热中肠。焉知二十载，重上君子堂。昔别君未婚，儿女忽成行。怡然敬父执，问我来何方？问答未及已，驱儿罗酒浆夜雨剪春韭，新炊间黄粱。主称会面难，一举累十觞。十觞亦不醉，感子故意长。明日隔山岳，世事两茫茫。",
         'is_published': True},
        {'title': '下终南山过斛斯山人宿置酒', 'poet': '李白',
         'poem': "暮从碧山下，山月随人归。却顾所来径，苍苍横翠微。相携及田家，童稚开荆扉。绿竹入幽径，青萝拂行衣。欢言得所憩，美酒聊共挥。长歌吟松风，曲尽河星稀。我醉君复乐，陶然共忘机。",
         'is_published': False},
        {'title': '塞下曲·其二', 'poet': '王昌龄',
         'poem': "饮马渡秋水，水寒风似刀。平沙日未没，黯黯见临洮。昔日长城战，咸言意气高。黄尘足今古，白骨乱蓬蒿。",
         'is_published': True},
        {'title': '饮马渡秋水，水寒风似刀。平沙日未没，黯黯见临洮。昔日长城战，咸言意气高。黄尘足今古，白骨乱蓬蒿。',
         'poet': '王昌龄', 'poem': "11111111111", 'is_published': False}
    ]

    # 查询的作业练习
    # from work.models import Customers
    # d1 = [
    #     [1, 'Tom', 'tom@example.com'],
    #     [2, 'Jane', 'Jane@example.com'],
    #     [3, 'Pater', 'peter@example.com'],
    #     [4, 'Susan', 'susan@example.com']]
    # for i in d1:
    #     Customers(id=i[0], name=i[1], email=i[2]).save()

    # d2 = [[1, 1, '2023-06-15', 100.00],
    #       [2, 1, '2023-06-16', 200.00],
    #       [3, 1, '2023-06-18', 50.00],
    #       [4, 2, '' '2023-06-02', 120.00],
    #       [5, 2, '2023-06-15', 70.00],
    #       [6, 3, '2023-06-01', 130.00],
    #       [7, 3, '2023-06-10', 60.00],
    #       [8, 4, '2023-06-19', 40.00],
    #       [9, 4, '2023-06-19', 50.00],
    #       [10, 4, '2023-06-19', 80.00]]
    # for i in d2:
    #     Orders(id=i[0],
    #            customer_id=Customers.objects.get(id=i[1]),
    #            date_ordered=i[2],
    #            total_amount=i[3]).save()

    from work.models import Orders, Customers
    from django.db.models import Count, Sum
    # 查询每个客户的订单总数(方法一)
    # data = Orders.objects.values('customer_id').annotate(count=Count('customer_id'))
    # print(data)
    # for i in data:
    #     print(i['count'], Customers.objects.get(id=i['customer_id']).name)

    # data = Orders.objects.annotate(orders=Count('customer_id'))
    # print(data)

    # 查询每个客户的订单总数(方法二)
    # result = Customers.objects.annotate(num_orders=Count('orders'))
    # print(result)
    # for i in result:
    #     print(i.id, i.name, i.num_orders)

    # 查询每个客户的总消费金额
    # data = Orders.objects.values('customer_id').annotate(sum_amount=Sum('total_amount'))
    # for i in data:
    #     print(i['sum_amount'], Customers.objects.get(id=i['customer_id']).name)

    # 查询最近一个月内的客户的订单数量
    # date = '2023-05-22 20:49:16.722549'.split()[0]
    # # 先过滤在进行分组聚合
    # data = Orders.objects.filter(date_ordered__gte=date).values('customer_id').annotate(count_id=Count('id'))
    # print(data)
    # for i in data:
    #     print(i['count_id'], Customers.objects.get(id=i['customer_id']).name)

    # 扩展内容真正计算时使用
    # import datetime
    # new_date = datetime.datetime.today() - datetime.timedelta(days=30)
    # print(new_date)

    # 查询订单数大于等于3的客户的数量
    # data = Orders.objects.values('customer_id').annotate(count_id=Count('id'))
    # data = data.filter(count_id__gte=3)
    # print(data)
    # for i in data:
    #     print(i['count_id'], Customers.objects.get(id=i['customer_id']).name)

    # 查询消费总金额大于 100 的客户数量
    # data = Orders.objects.values('customer_id').annotate(sum_amount=Sum('total_amount'))
    # data = data.filter(sum_amount__gt=100)
    # print(data)
    # for i in data:
    #     print(i['sum_amount'], Customers.objects.get(id=i['customer_id']).name)

    return render(request, 'homework.html', {'blog_entries': blog_entries})
